const db = require('../config/db');

// 根据学生ID获取所有成绩概览
exports.getGradesByStudentId = async (studentNo) => {
    console.log('获取学生成绩:', studentNo);
    try {
        // 从students表中查询学生信息
        const [student] = await db.query(`
            SELECT id, s_name
            FROM students 
            WHERE s_no = ?
        `, [studentNo]);
        const studentId = student[0].id;
        console.log('学生信息:', student[0].id);

        // 第一步：查询学生所属的班级列表
        const [classes] = await db.query(`
            SELECT class_id 
            FROM class_students 
            WHERE student_id = ?
        `, [studentId]);

        if (!classes || classes.length === 0) {
            console.log('该学生未加入任何班级');
            return [];
        }

        const classIds = classes.map(c => c.class_id);
        console.log('学生所在班级ID:', classIds);

        // 第二步：查询这些班级相关的实验列表
        const [experiments] = await db.query(`
            SELECT DISTINCT e.id AS experiment_id, e.title, e.course_name
            FROM experiment_classes ce
            JOIN experiments e ON ce.experiment_id = e.id
            WHERE ce.class_id IN (?)
        `, [classIds]);

        console.log('班级关联的实验:', experiments);

        if (!experiments || experiments.length === 0) {
            console.log('班级未布置任何实验');
            return [];
        }

        // 按课程分组
        const courseMap = new Map();
        
        for (const exp of experiments) {
            // 初始化课程数据
            if (!courseMap.has(exp.course_name)) {
                courseMap.set(exp.course_name, {
                    courseName: exp.course_name,
                    experimentGrades: []
                });
            }
            
            const course = courseMap.get(exp.course_name);
            
            // 获取该实验的评分标准
            const [criteria] = await db.query(`
                SELECT id, name, proportion 
                FROM experiment_criteria 
                WHERE experiment_id = ?
            `, [exp.experiment_id]);

            const parts = [];

            // 处理每个评分项的成绩
            for (const criterion of criteria) {
                // 查询该评分项的成绩
                const [scoreData] = await db.query(`
                    SELECT score 
                    FROM experiment_scores 
                    WHERE experiment_id = ? 
                      AND criteria_id = ? 
                      AND student_id = ?
                `, [exp.experiment_id, criterion.id, studentId]);

                // 如果无成绩记录则设为0
                const score = scoreData[0]?.score || 0;
                
                parts.push({
                    partName: criterion.name,
                    score: score,
                    maxScore: 100, // 满分100分
                    weight: criterion.proportion / 100
                });
            }

            course.experimentGrades.push({
                experimentId: exp.experiment_id,
                title: exp.title,
                parts: parts
            });
        }

        return Array.from(courseMap.values());
    } catch (error) {
        console.error('获取学生成绩失败:', error);
        throw error;
    }
};

// 根据学生ID和课程名称获取课程详细成绩
exports.getCourseGrade = async (studentId, courseName) => {
    try {
        // 直接查询学生在该课程的所有实验
        const [experiments] = await db.query(`
            SELECT DISTINCT e.id AS experiment_id, e.title
            FROM experiment_scores es
            JOIN experiments e ON es.experiment_id = e.id
            WHERE es.student_id = ? AND e.course_name = ?
        `, [studentId, courseName]);

        if (!experiments || experiments.length === 0) {
            return null;
        }

        const experimentGrades = [];

        // 获取每个实验的成绩
        for (const exp of experiments) {
            // 获取该实验的评分标准
            const [criteria] = await db.query(`
                SELECT id, name, proportion 
                FROM experiment_criteria 
                WHERE experiment_id = ?
            `, [exp.experiment_id]);

            const parts = [];

            for (const criterion of criteria) {
                const [scoreData] = await db.query(`
                    SELECT score 
                    FROM experiment_scores 
                    WHERE experiment_id = ? 
                      AND criteria_id = ? 
                      AND student_id = ?
                `, [exp.experiment_id, criterion.id, studentId]);

                const score = scoreData[0]?.score || 0;

                parts.push({
                    partName: criterion.name,
                    score: score,
                    maxScore: 100, // 满分100分
                    weight: criterion.proportion / 100
                });
            }

            experimentGrades.push({
                id: exp.experiment_id,
                title: exp.title,
                parts: parts
            });
        }

        return {
            courseName: courseName,
            experimentGrades: experimentGrades
        };
    } catch (error) {
        console.error('获取课程详细成绩失败:', error);
        throw error;
    }
};